package com.ambow.dao;


import java.util.List;
import java.util.Date;

import org.hibernate.Query;
import org.hibernate.Session;
import org.junit.Test;

import com.ambow.Entity.StockReceive;
import com.ambow.Entity.TextBook;
import com.ambow.utils.HibernateUtil;


public class Book_query {
	/**
	 * 
	 * @param book_name
	 * @return
	 * 根据书名查找书 的信息，返回list
	 */

	public List bookinfo(String book_name){
		List list=null;

		HibernateUtil hibernateUtil = new HibernateUtil();
	    Session session = hibernateUtil.getSession();
//		session.beginTransaction();
		Query query = session.createQuery("from TextBook t where t.name='"+book_name+"'");
		list=query.list();
		if(list!=null){
			return list;
		
//			for(int i=0; i<list.size(); i++){
//				TextBook op = (TextBook) list.get(i);
//				System.out.println(op.getRemark());
//			}
		}
		session.getTransaction().commit();
		return list;
	}
    /**
     * 
     * @return
     * 查询书名、书籍数量、供应商姓名、入库时间、价格，返回list
     */
	public List orderQuery(){
		List list=null;
		HibernateUtil hibernateUtil = new HibernateUtil();
	    Session session = hibernateUtil.getSession();
	
//		Query query = session.createQuery("select tb.book_name, sr.amount, s.name, sr.receive_date, tb.price from StockReceive sr, TextBook tb, Supplier s where s.name='"+name+"' and s.id=sr.supplier_id and tb.id=sr.textbook_id");
		Query query = session.createSQLQuery("select t.name as Tname, ss.amount, ss.name, ss.receiveDate, t.price from (select s.name , r.receiveDate, r.amount, r.textBook_id  from Supplier s inner join StockReceive r on r.supplier_id=s.id) ss inner join TextBook t on t.id=ss.textBook_id ").addEntity(TextBook.class).addEntity(StockReceive.class);
		//SELECT n.AMOUNT,n.RECEIVE_DATE, bookName,n.price, m.NAME   FROM (SELECT  s.AMOUNT,s.RECEIVE_DATE,t.name,t.price,s.SUPPLIER_ID FROM stockreceive s INNER JOIN textbook t on s.TEXTBOOK_ID = t.ID) n INNER JOIN supplier m WHERE n.SUPPLIER_ID = m.ID
		 
		list=query.list();
		if(list!=null){
			return list;
		}
		session.getTransaction().commit();
		session.close();
		return list;
	}
	/**
	 * 
	 * @return
	 * 查找书名、书籍数量、供应商姓名、入库时间、价格.返回list
	 */
	public List orderQuery2(){
		List list=null;
		HibernateUtil hibernateUtil = new HibernateUtil();
	    Session session = hibernateUtil.getSession();
//		session.beginTransaction();
//		Query query = session.createQuery("select tb.book_name, sr.amount, s.name, sr.receive_date, tb.price from StockReceive sr, TextBook tb, Supplier s where s.name='"+name+"' and s.id=sr.supplier_id and tb.id=sr.textbook_id");
		Query query = session.createSQLQuery("select t.name, ss.amount, ss.name, ss.receiveDate, t.price from (select s.name , r.receiveDate, r.amount, r.textBook_id  from Supplier s inner join StockReceive r on r.supplier_id=s.id) ss inner join TextBook t on t.id=ss.textBook_id ");


		//SELECT n.AMOUNT,n.RECEIVE_DATE, bookName,n.price, m.NAME   FROM (SELECT  s.AMOUNT,s.RECEIVE_DATE,t.name,t.price,s.SUPPLIER_ID FROM stockreceive s INNER JOIN textbook t on s.TEXTBOOK_ID = t.ID) n INNER JOIN supplier m WHERE n.SUPPLIER_ID = m.ID

		
		list=query.list();
		if(list!=null){
			return list;
		}
		session.getTransaction().commit();
		session.close();
		return list;
	}
	/**
	 * 
	 * @param name
	 * @return
	 * 根据供应商姓名查找书名、书籍数量、供应商姓名、入库时间、价格。返回list
	 */
	public List orderQuery1(String name){
		List list=null;
		HibernateUtil hibernateUtil = new HibernateUtil();
	    Session session = hibernateUtil.getSession();
//		session.beginTransaction();
		Query query = session.createSQLQuery("select t.name, ss.amount, ss.name, ss.receiveDate, t.price from (select s.name , r.receiveDate, r.amount, r.textBook_id  from Supplier s inner join StockReceive r on r.supplier_id=s.id) ss inner join TextBook t on t.id=ss.textBook_id where ss.name='"+name+"'");
		list=query.list();
		if(list!=null){
			return list;
		}
		session.getTransaction().commit();
		session.close();
		return list;
	}
	/**
	 * 
	 * @param name
	 * @return
	 * 同orderQuery2，根据供应商姓名查找书名、书籍数量、供应商姓名、入库时间、价格。返回list
	 */
	public List orderQuery3(String name){
		List list=null;
		HibernateUtil hibernateUtil = new HibernateUtil();
	    Session session = hibernateUtil.getSession();
//		session.beginTransaction();
		Query query = session.createSQLQuery("select t.name, ss.amount, ss.name, ss.receiveDate, t.price from (select s.name , r.receiveDate, r.amount, r.textBook_id  from Supplier s inner join StockReceive r on r.supplier_id=s.id) ss inner join TextBook t on t.id=ss.textBook_id where ss.name='"+name+"'");
		list=query.list();
		if(list!=null){
			return list;
		}
		session.getTransaction().commit();
		session.close();
		return list;
	}
	

}
